package com.liferay.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.liferay.dao.ConnectDao;
import com.liferay.dao.RegisterSubjectDao;
import com.liferay.model.Subject;

public class RegisterSubjectDaoImpl implements RegisterSubjectDao {

	private PreparedStatement pre;
	private ResultSet rs;
	private Connection conn;

	@Override
	public List<Subject> getListSubjectOpen(long userId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT nso.fkSubject, nso.idSubjectOpen, ns.nameSubject, tk1.firstName, tk1.middleName, tk1.lastName, nso.date_start, nso.date_end, class.nameOfClass, room.nameRoom, lesson.rankOfLesson, nso.weekdays FROM user_ u, users_facultys uf, nlu_subject ns, nlu_subjectopen nso, nlu_class class, nlu_room room, nlu_lesson lesson, (SELECT u.firstName, u.middleName, u.lastName, u.userId FROM user_ u WHERE userId IN (SELECT nso.fkTeacher FROM user_ u, users_facultys uf, nlu_subject ns, nlu_subjectopen nso ");
		sql.append(" WHERE u.userId=? and u.userId=uf.userId and ns.fkFaculty=uf.idFaculty and ns.idSubject=nso.fkSubject)) as tk1 ");
		sql.append(" WHERE u.userId=? and u.userId=uf.userId and ns.fkFaculty=uf.idFaculty and ns.idSubject=nso.fkSubject and nso.fkTeacher=tk1.userId and nso.fkClass=class.idClass and nso.fkRoom=room.idRoom and nso.fkLesson=lesson.idLesson");
		sql.append(" and nso.idSubjectOpen not in (SELECT nso.idSubjectOpen FROM nlu_subjectdetail ns, nlu_subjectopen nso ");
		sql.append("  WHERE ns.fkStudent=? and ns.fkSubjectOpen=nso.idSubjectOpen and nso.fkYearOfSemester=(SELECT MAX(id) FROM nlu_yearofsemester)) ");
		conn = ConnectDao.getConnection();
		List<Subject> list = new ArrayList<Subject>();
		try {
			pre = conn.prepareStatement(sql.toString());
			pre.setLong(1, userId);
			pre.setLong(2, userId);
			pre.setLong(3, userId);
			rs = pre.executeQuery();
			while (rs.next()) {
				Subject subject = new Subject();
				subject.setIdSubject(rs.getLong("fkSubject"));
				subject.setIdSubjectOpen(rs.getLong("idSubjectOpen"));
				subject.setNameSubject(rs.getString("nameSubject"));
				subject.setFullNameTeacher(rs.getString("lastName") + " "
						+ rs.getString("middleName") + " "
						+ rs.getString("firstName"));
				subject.setStartDate(rs.getString("date_start"));
				subject.setEndDate(rs.getString("date_end"));
				subject.setNameOfClass(rs.getString("nameOfClass"));
				subject.setNameOfRoom(rs.getString("nameRoom"));
				subject.setRankOfLesson(rs.getString("rankOfLesson"));
				subject.setWeekdays(rs.getString("weekdays"));
				list.add(subject);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		}
		return list;
	}

	@Override
	public boolean registerSubject(Subject subject) {
		StringBuffer checkCount = new StringBuffer();
		checkCount
				.append("SELECT count(ns.numberOfStudy) as number FROM nlu_subjectdetail ns, nlu_subjectopen nso, nlu_subject sb ");
		checkCount
				.append(" WHERE sb.idSubject=? and sb.idSubject=nso.fkSubject and nso.idSubjectOpen=ns.fkSubjectOpen and  ns.fkStudent=? ");
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(checkCount.toString());
			pre.setLong(1, subject.getIdSubject());
			pre.setLong(2, subject.getFkStudent());
			rs = pre.executeQuery();
			int count = 0;
			while (rs.next()) {
				count = rs.getInt("number");
				break;
			}
			rs.close();
			pre.close();
			StringBuffer sqlSave = new StringBuffer();
			sqlSave.append("INSERT INTO nlu_subjectdetail(fkSubjectOpen,fkStudent,numberOfStudy) VALUES(?,?,?);");
			PreparedStatement pre1 = conn.prepareStatement(sqlSave.toString());
			pre1.setLong(1, subject.getIdSubjectOpen());
			pre1.setLong(2, subject.getFkStudent());
			pre1.setLong(3, count++);
			pre1.executeUpdate();
			pre1.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public List<Subject> getListSubjectOpenRegister(long userId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT nso.fkSubject, nso.idSubjectOpen, ns.nameSubject, tk1.firstName, tk1.middleName, tk1.lastName, nso.date_start, nso.date_end, class.nameOfClass, room.nameRoom, lesson.rankOfLesson, nso.weekdays, ns.numberOfCredit FROM user_ u, users_facultys uf, nlu_subject ns, nlu_subjectopen nso, nlu_class class, nlu_room room, nlu_lesson lesson, (SELECT u.firstName, u.middleName, u.lastName, u.userId FROM user_ u WHERE userId IN (SELECT nso.fkTeacher FROM user_ u, users_facultys uf, nlu_subject ns, nlu_subjectopen nso ");
		sql.append(" WHERE u.userId=? and u.userId=uf.userId and ns.fkFaculty=uf.idFaculty and ns.idSubject=nso.fkSubject)) as tk1 ");
		sql.append(" WHERE u.userId=? and u.userId=uf.userId and ns.fkFaculty=uf.idFaculty and ns.idSubject=nso.fkSubject and nso.fkTeacher=tk1.userId and nso.fkClass=class.idClass and nso.fkRoom=room.idRoom and nso.fkLesson=lesson.idLesson");
		sql.append(" and nso.idSubjectOpen in (SELECT nso.idSubjectOpen FROM nlu_subjectdetail ns, nlu_subjectopen nso ");
		sql.append("  WHERE ns.fkStudent=? and ns.fkSubjectOpen=nso.idSubjectOpen and nso.fkYearOfSemester=(SELECT MAX(id) FROM nlu_yearofsemester)) ");
		conn = ConnectDao.getConnection();
		List<Subject> list = new ArrayList<Subject>();
		try {
			pre = conn.prepareStatement(sql.toString());
			pre.setLong(1, userId);
			pre.setLong(2, userId);
			pre.setLong(3, userId);
			rs = pre.executeQuery();
			while (rs.next()) {
				Subject subject = new Subject();
				subject.setIdSubject(rs.getLong("fkSubject"));
				subject.setIdSubjectOpen(rs.getLong("idSubjectOpen"));
				subject.setNameSubject(rs.getString("nameSubject"));
				subject.setFullNameTeacher(rs.getString("lastName") + " "
						+ rs.getString("middleName") + " "
						+ rs.getString("firstName"));
				subject.setStartDate(rs.getString("date_start"));
				subject.setEndDate(rs.getString("date_end"));
				subject.setNameOfClass(rs.getString("nameOfClass"));
				subject.setNameOfRoom(rs.getString("nameRoom"));
				subject.setRankOfLesson(rs.getString("rankOfLesson"));
				subject.setWeekdays(rs.getString("weekdays"));
				subject.setNumberOfCredit(rs.getInt("numberOfCredit"));
				list.add(subject);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		}
		return list;
	}

	@Override
	public boolean deleteSubject(Subject subject) {
		String sql = "DELETE FROM nlu_subjectdetail WHERE fkSubjectOpen=? and fkStudent=?;";
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(sql);
			pre.setLong(1, subject.getIdSubjectOpen());
			pre.setLong(2, subject.getFkStudent());
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public List<Subject> getListSubjectOpenRegisterByEmail(String email) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT u.userId,nso.fkSubject,nso.idSubjectOpen, ns.nameSubject, tk1.firstName, tk1.middleName, tk1.lastName, nso.date_start, nso.date_end, class.nameOfClass, room.nameRoom, lesson.rankOfLesson, nso.weekdays, ns.numberOfCredit, nsd.mark ");
		sql.append(" FROM user_ u, users_facultys uf, nlu_subject ns, nlu_subjectopen nso, nlu_class class, nlu_room room, nlu_lesson lesson,nlu_subjectdetail nsd, ");
		sql.append(" (SELECT u.firstName, u.middleName, u.lastName, u.userId FROM user_ u WHERE userId IN (SELECT nso.fkTeacher FROM user_ u, users_facultys uf, nlu_subject ns, nlu_subjectopen nso ");
		sql.append(" WHERE u.emailAddress=? and u.userId=uf.userId and ns.fkFaculty=uf.idFaculty and ns.idSubject=nso.fkSubject)) as tk1 ");
		sql.append(" WHERE u.emailAddress=? and u.userId=uf.userId and ns.fkFaculty=uf.idFaculty and ns.idSubject=nso.fkSubject and nso.fkTeacher=tk1.userId and nso.fkClass=class.idClass and nso.fkRoom=room.idRoom and nso.fkLesson=lesson.idLesson and nsd.fkSubjectOpen=nso.idSubjectOpen ");
		sql.append(" and nso.idSubjectOpen in (SELECT nso.idSubjectOpen FROM nlu_subjectdetail ns, nlu_subjectopen nso , user_ u ");
		sql.append(" WHERE u.emailAddress=? and u.userId=ns.fkStudent and ns.fkSubjectOpen=nso.idSubjectOpen) ");
		conn = ConnectDao.getConnection();
		List<Subject> list = new ArrayList<Subject>();
		try {
			pre = conn.prepareStatement(sql.toString());
			pre.setString(1, email);
			pre.setString(2, email);
			pre.setString(3, email);
			rs = pre.executeQuery();
			while (rs.next()) {
				Subject subject = new Subject();
				subject.setFkStudent(rs.getLong("userId"));
				subject.setIdSubject(rs.getLong("fkSubject"));
				subject.setIdSubjectOpen(rs.getLong("idSubjectOpen"));
				subject.setNameSubject(rs.getString("nameSubject"));
				subject.setFullNameTeacher(rs.getString("lastName") + " "
						+ rs.getString("middleName") + " "
						+ rs.getString("firstName"));
				subject.setStartDate(rs.getString("date_start"));
				subject.setEndDate(rs.getString("date_end"));
				subject.setNameOfClass(rs.getString("nameOfClass"));
				subject.setNameOfRoom(rs.getString("nameRoom"));
				subject.setRankOfLesson(rs.getString("rankOfLesson"));
				subject.setWeekdays(rs.getString("weekdays"));
				subject.setNumberOfCredit(rs.getInt("numberOfCredit"));
				subject.setMark(rs.getDouble("mark"));
				list.add(subject);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		}
		return list;
	}

	@Override
	public boolean updateSubjectMark(Subject subject) {
		String sql = "UPDATE nlu_subjectdetail SET mark=? WHERE fkSubjectOpen=? and fkStudent=?;";
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(sql);
			pre.setDouble(1, subject.getMark());
			pre.setLong(2, subject.getIdSubjectOpen());
			pre.setLong(3, subject.getFkStudent());
			pre.executeUpdate();
			pre.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
